
clear
set more off

use ${wd}/cps/fullCPS.dta,clear

// removing missing cities
replace city=. if city==0

// create our city definition
g city_plus=city
g city_plus2=city

// Create Proper defs for cities
replace city_plus=	58	if state==	12	& city==.
replace city_plus=	59	if state==	14	& city==.
replace city_plus=	60	if state==	21	& city==.
replace city_plus=	61	if state==	22	& city==.
replace city_plus=	62	if state==	23	& city==.
replace city_plus=	63	if state==	31  & city==.
replace city_plus=	64	if state==	32	& city==.
replace city_plus=	65	if state==	33	& city==.
replace city_plus=	66	if state==	34	& city==.
replace city_plus=	67	if state==	35	& city==.
replace city_plus=	68	if state==	41	& city==.
replace city_plus=	69	if state==	43	& city==.
replace city_plus=	70	if state==	47	& city==.
replace city_plus=	71	if state==	52	& city==.
replace city_plus=	72	if state==	53	& city==.
replace city_plus=	73	if state==	54	& city==.
replace city_plus=	74	if state==	56	& city==.
replace city_plus=	75	if state==	58	& city==.
replace city_plus=	76	if state==	59	& city==.
replace city_plus=	77	if state==	61	& city==.
replace city_plus=	78	if state==	63	& city==.
replace city_plus=	79	if state==	72	& city==.
replace city_plus=	80	if state==	74	& city==.
replace city_plus=	81	if state==	84	& city==.
replace city_plus=	82	if state==	91	& city==.
replace city_plus=	83	if state==	92	& city==.
replace city_plus=	84	if state==	93	& city==.


replace city_plus2=	58	if state==	11	& city==.
replace city_plus2=	59	if state==	12	& city==.
replace city_plus2=	60	if state==	13	& city==.
replace city_plus2=	61	if state==	14	& city==.
replace city_plus2=	62	if state==	15	& city==.
replace city_plus2=	63	if state==	16	& city==.
replace city_plus2=	64	if state==	21	& city==.
replace city_plus2=	65	if state==	22	& city==.
replace city_plus2=	66	if state==	23	& city==.
replace city_plus2=	67	if state==	31	& city==.
replace city_plus2=	68	if state==	32	& city==.
replace city_plus2=	69	if state==	33	& city==.
replace city_plus2=	70	if state==	34	& city==.
replace city_plus2=	71	if state==	35	& city==.
replace city_plus2=	72	if state==	41	& city==.
replace city_plus2=	73	if state==	42	& city==.
replace city_plus2=	74	if state==	43	& city==.
replace city_plus2=	75	if state==	44	& city==.
replace city_plus2=	76	if state==	45	& city==.
replace city_plus2=	77	if state==	46	& city==.
replace city_plus2=	78	if state==	47	& city==.
replace city_plus2=	79	if state==	51	& city==.
replace city_plus2=	80	if state==	52	& city==.
replace city_plus2=	81	if state==	53	& city==.
replace city_plus2=	82	if state==	54	& city==.
replace city_plus2=	83	if state==	55	& city==.
replace city_plus2=	84	if state==	56	& city==.
replace city_plus2=	85	if state==	57	& city==.
replace city_plus2=	86	if state==	58	& city==.
replace city_plus2=	87	if state==	59	& city==.
replace city_plus2=	88	if state==	61	& city==.
replace city_plus2=	89	if state==	62	& city==.
replace city_plus2=	90	if state==	63	& city==.
replace city_plus2=	91	if state==	64	& city==.
replace city_plus2=	92	if state==	71	& city==.
replace city_plus2=	93	if state==	72	& city==.
replace city_plus2=	94	if state==	73	& city==.
replace city_plus2=	95	if state==	74	& city==.
replace city_plus2=	96	if state==	81	& city==.
replace city_plus2=	97	if state==	82	& city==.
replace city_plus2=	98	if state==	83	& city==.
replace city_plus2=	99	if state==	84	& city==.
replace city_plus2=	100	if state==	85	& city==.
replace city_plus2=	101	if state==	86	& city==.
replace city_plus2=	102	if state==	87	& city==.
replace city_plus2=	103	if state==	88	& city==.
replace city_plus2=	104	if state==	91	& city==.
replace city_plus2=	105	if state==	92	& city==.
replace city_plus2=	106	if state==	93	& city==.
replace city_plus2=	107	if state==	94	& city==.
replace city_plus2=	108	if state== 95	& city==.

cap label drop state
cap label drop city
do "${cps}/programs/labels_state.do"
do "${cps}/programs/labels_city_final.do"

label val state state
label val city city
label val city_plus city_plus
label val city_plus2 city_plus2

cap drop if ${area}==.


collapse (mean) union (rawsum) n = wgt  [aw=wgt] , by(year $area) 

save ${wd}/cps/union_city_trends.dta,replace
















